// ==========================================================================
// 所属模块：core - gen - sql
// 生成日期：2024-02-19 00:24:00 +0800 CST
// 生成路径: dm/sql/sql.js
// 生成人：Cato
// 功能描述：定义全局通用的SQL的CURD功能封装，包含分页列表和模版查询、数据增删等等。（注册于global.js内)
// ==========================================================================

// 系统的sql数据库连接名为 system_sql

// 连接测试数据库
// var test_sql = SQL.new(
//     'mydb',
//     'mysql',
//     'root:123456@tcp(127.0.0.1:3306)/test_db'
// )

var stmt
var result

// 分页数据
function genSelectData(chooseData, placeholdersObj) {
    const newPlaceholders = placeholdersObj.placeholders;
    if (newPlaceholders != '') {
        const tempParams = placeholdersObj.tempParams;
        return {
            query: `SELECT ${chooseData} FROM ${TABLE_NAME} WHERE delete_time IS NULL AND ${newPlaceholders}`,
            params: [...tempParams],
        };
    } else {
        return {
            query: `SELECT ${chooseData} FROM ${TABLE_NAME} WHERE delete_time IS NULL`,
            params: [],
        };
    }
}

// 列表查询总数居
function genCountTotal(placeholdersObj) {
    const newPlaceholders = placeholdersObj.placeholders;
    if (newPlaceholders != '') {
        const tempParams = placeholdersObj.tempParams;
        return {
            query: `SELECT COUNT(*) AS count FROM ${TABLE_NAME} WHERE delete_time IS NULL AND ${newPlaceholders}`,
            params: [...tempParams],
        };
    } else {
        return {
            query: `SELECT COUNT(*) AS count FROM ${TABLE_NAME} WHERE delete_time IS NULL`,
            params: [],
        };
    }
}


/**比较两个对象的 sort 属性*/
function compareSorts(a, b) {
    // 如果 sort 属性为 null，则将其视为无穷大
    if (!a.sort || !b.sort) {
        return 0; // 不参与排序
    }
    // 比较 sort 属性
    return a.sort - b.sort;
}

// 分页列表查询

function genListPage(chooseData, placeholdersObj) {
    // 总数据
    const ROWS_COUNT = genCountTotal(placeholdersObj);
    ROWS_COUNT.query += searchStr
    stmt = ROWS_COUNT.query

    if (TABLE_INFO.before_hook) {
        runtime.call(TABLE_INFO.before_hook)
    }

    SQL_STMTS.push(stmt)
    result = SQL.query(DB_NAME, stmt, ...ROWS_COUNT.params);

    if (TABLE_INFO.after_hook) {
        runtime.call(TABLE_INFO.after_hook)
    }

    // 总数据
    const count = result
    // 分页数据
    const LIST_DATA = genSelectData(chooseData, placeholdersObj);
    if (parseInt(params.pageSize) > 1000) {
        response.status.code(413)
        return false
    }

    if (params.pageNum && params.pageSize) {
        searchStr += ` LIMIT ${(params.pageNum - 1) * parseInt(params.pageSize)}, ${parseInt(params.pageSize)}`
    }
    LIST_DATA.query += searchStr

    stmt = LIST_DATA.query

    // console.log(LIST_DATA.query)

    if (TABLE_INFO.before_hook) {
        runtime.call(TABLE_INFO.before_hook)
    }

    SQL_STMTS.push(stmt)
    result = SQL.query(DB_NAME, stmt, ...LIST_DATA.params);

    const coverRes = result.sort(compareSorts)
    result = { result: coverRes, count: count[0].count, pageNum: params.pageNum, pageSize: params.pageSize }

    if (TABLE_INFO.after_hook) {
        runtime.call(TABLE_INFO.after_hook)
    }

    return result;
}

'use api/dm/struct/table.js'

// 修改数据
function sqlGenUpdate() {
    const data = payload.get().body.toObject()
    let filter = payload.get().query[COLUMN_KEY]
    // 兼容 /user/1 这种通过 path 传值的方式
    if (filter == undefined) {
        filter = payload.get().path.split("/").pop();
    }
    var cols = []
    TABLE_STRUCT.forEach(item => {
        if (item.is_edit == 1) {
            cols.push(item.column_name)
        }
    })
    var updateArr = []
    var dataArr = []
    Object.keys(data).forEach(key => {
        if (cols.includes(camelToUnderscore(key))) {
            if (data[key] == 'null' || data[key] == null) {
                updateArr.push(`${camelToUnderscore(key)} =null`)
            } else {
                dataArr.push(data[key])
                updateArr.push(`${camelToUnderscore(key)} =?`)
            }

        }
    })
    updateArr.join(',')
    if (updateArr !== '') {
        stmt = `UPDATE ${TABLE_NAME} SET ${updateArr} WHERE ${COLUMN_KEY} = ?`

        if (TABLE_INFO.before_hook) {
            runtime.call(TABLE_INFO.before_hook)
        }

        payload.add.header("sql_exec", stmt)
        result = SQL.exec(DB_NAME, stmt, ...dataArr, filter)

        if (TABLE_INFO.after_hook) {
            runtime.call(TABLE_INFO.after_hook)
        }
        if (result) {
            return okMsg(result)
        }
    }
    return errMsg(507, '参数错误')
}

// 删除数据
function sqlGenDelete() {
    const findData = TABLE_STRUCT.find(item => item.column_name == COLUMN_KEY) || {}
    const parName = findData?.field_name || underscoreToCamel(COLUMN_KEY)
    const ids = payload.get().query[parName + '[]']
    const tempParams = []
    for (var i = 0; i < ids.length; i++) {
        tempParams.push('?')
    }
    stmt = `UPDATE ${TABLE_NAME} SET delete_time = NOW() WHERE ${COLUMN_KEY} IN (${tempParams.join(',')})`
    if (TABLE_INFO.before_hook) {
        result = runtime.call(TABLE_INFO.before_hook)
    }

    // let sql = `DELETE FROM ${TABLE_NAME} WHERE ${COLUMN_KEY} IN (${tempParams.join(',')})`
    SQL_STMTS.push(stmt)
    result = SQL.exec(
        DB_NAME,
        stmt, ...ids
    )

    if (TABLE_INFO.after_hook) {
        runtime.call(TABLE_INFO.after_hook)
    }

    if (result) {
        return okMsg(result)
    }

    return errMsg(507, '参数错误')
}

let searchStr = ''
// 获取数据
function sqlGenSelect() {
    let userAccess
    // 判断用户表权限
    if (TABLE_INFO.is_acl && !ALLOW_PUBLIC_ACCESS) {
        let udr = []
        udr.push(`'${USER_INFO.user_id}'`)
        udr.push(`'${USER_INFO.role_id}'`)
        udr.push(`'${USER_INFO.organization_id}'`)

        let columnIds = []
        TABLE_STRUCT.forEach(item => {
            if (item.is_acl == 1) {
                columnIds.push(`'${item.column_id}'`)
            }

            if (item.search_value) {
                searchStr += ` AND ${item.column_name} ${sql_operator[item.query_type]} '${item.search_value}'`
            }
        })

        userAccess = SQL.query(
            DB_NAME,
            `SELECT DISTINCT column_name FROM dm_table_columns_acl WHERE column_id IN (${columnIds.split(',')}) AND access_id IN (${udr.split(',')}) AND level > 0 AND delete_time IS NULL;`,
            TABLE_INFO.table_id
        )
    } else {
        TABLE_STRUCT.forEach(item => {
            if (item.search_value) {
                searchStr += ` AND ${item.column_name} ${sql_operator[item.query_type]} '${item.search_value}'`
            }
        })
    }

    let value = payload.get().query[COLUMN_KEY]
    // 兼容 /user/1 这种通过 path 传值的方式
    if (!value) {
        // 声明是否使用上一级的脚本
        var useParent = scriptPath != payload.get().path.substring(1)

        if (useParent) {
            value = payload.get().path.split("/").pop();
        } else {
            return sqlGenList();
        }
    }

    var cols = []
    for (var i = 0; i < TABLE_STRUCT.length; i++) {
        if (TABLE_STRUCT[i].is_show == 1) {
            if (TABLE_STRUCT[i].is_acl == 1 && !userAccess.includes(TABLE_STRUCT[i].column_name)) {
                continue
            }
            cols.push(TABLE_STRUCT[i].column_name + ' as ' + TABLE_STRUCT[i].field_name)
        }
    }

    const chooseData = cols.join(',');
    // const chooseData = "*";

    stmt = `SELECT ${chooseData} FROM ${TABLE_NAME} WHERE delete_time IS NULL AND ${COLUMN_KEY} = ? ${searchStr}`

    if (TABLE_INFO.before_hook) {
        runtime.call(TABLE_INFO.before_hook)
    }

    SQL_STMTS.push(stmt)
    result = SQL.query(
        DB_NAME,
        stmt, value
    )

    if (TABLE_INFO.after_hook) {
        runtime.call(TABLE_INFO.after_hook)
    }

    // let result = []
    // res.forEach(t => {
    //     const newObj = Object.keys(t).reduce((acc, key) => {
    //         acc[underscoreToCamel(key.toLowerCase())] = t[key];
    //         return acc;
    //     }, {});
    //     result.push(newObj)
    // });
    if (result) {
        return okMsg(result)
    }
    return errMsg(404, '查询失败')
}

var sql_operator = {
    EQ: '=',
    NE: '!=',
    GT: '>',
    LT: '<',
    GTE: '>=',
    LTE: '<=',
    IN: 'IN',
    ON: 'ON',
    NOT: 'NOT',
    LIKE: 'LIKE',
    NL: 'NOT LIKE',
    NI: 'NOT IN'
}

'use api/dm/sql/formatCondition.js'
'use api/lib/requestQueryFormatSingle.js'
'use api/lib/camelAndUnderscore.js'

// 获取sql查询列表
function sqlGenList() {
    let userAccess    // 判断用户表权限
    if (TABLE_INFO.is_acl && !ALLOW_PUBLIC_ACCESS) {
        let udr = []
        udr.push(`'${USER_INFO.user_id}'`)
        udr.push(`'${USER_INFO.role_id}'`)
        udr.push(`'${USER_INFO.organization_id}'`)

        let columnIds = []
        TABLE_STRUCT.forEach(item => {
            if (item.is_acl == 1) {
                columnIds.push(`'${item.column_id}'`)
            }
            if (item.search_value) {
                searchStr += ` AND ${item.column_name} ${sql_operator[item.query_type]} '${item.search_value}'`
            }
        })

        userAccess = SQL.query(
            DB_NAME,
            `SELECT DISTINCT column_name FROM dm_table_columns_acl WHERE column_id IN (${columnIds.split(',')}) AND access_id IN (${udr.split(',')}) AND level > 0 AND delete_time IS NULL;`,
            TABLE_INFO.table_id
        )
    } else {
        TABLE_STRUCT.forEach(item => {
            if (item.search_value) {
                searchStr += ` AND ${item.column_name} ${sql_operator[item.query_type]} '${item.search_value}'`
            }
        })
    }

    var cols = []
    var groups = []
    var orders = []
    var distincts = []
    for (var i = 0; i < TABLE_STRUCT.length; i++) {
        if (TABLE_STRUCT[i].is_list == 1) {
            if (TABLE_STRUCT[i].is_acl == 1 && !userAccess.includes(TABLE_STRUCT[i].column_name)) {
                continue
            }

            // 运算规则 0 SUM 1 COUNT 2 AVG 3 MAX 4 MIN 5 VARIANCE 6 STDDEV 7 GROUP_CONCAT
            switch (TABLE_STRUCT[i].calc_type) {
                case 0:
                    cols.push(`SUM(${TABLE_STRUCT[i].column_name}) as ${TABLE_STRUCT[i].field_name}`)
                    break
                case 1:
                    cols.push(`COUNT(${TABLE_STRUCT[i].column_name}) as ${TABLE_STRUCT[i].field_name}`)
                    break
                case 2:
                    cols.push(`AVG(${TABLE_STRUCT[i].column_name}) as ${TABLE_STRUCT[i].field_name}`)
                    break
                case 3:
                    cols.push(`MAX(${TABLE_STRUCT[i].column_name}) as ${TABLE_STRUCT[i].field_name}`)
                    break
                case 4:
                    cols.push(`MIN(${TABLE_STRUCT[i].column_name}) as ${TABLE_STRUCT[i].field_name}`)
                    break
                case 5:
                    cols.push(`VARIANCE(${TABLE_STRUCT[i].column_name}) as ${TABLE_STRUCT[i].field_name}`)
                    break
                case 6:
                    cols.push(`STDDEV(${TABLE_STRUCT[i].column_name}) as ${TABLE_STRUCT[i].field_name}`)
                    break
                case 7:
                    cols.push(`GROUP_CONCAT(${TABLE_STRUCT[i].column_name}) as ${TABLE_STRUCT[i].field_name}`)
                    break
                default:
                    cols.push(TABLE_STRUCT[i].column_name + ' as ' + TABLE_STRUCT[i].field_name)
                    break
            }

            if (TABLE_STRUCT[i].is_distinct == 1) {
                distincts.push(`${TABLE_STRUCT[i].field_name}`)
            }

            if (TABLE_STRUCT[i].is_group == 1) {
                groups.push(`${TABLE_STRUCT[i].field_name}`)
            }

            if (TABLE_STRUCT[i].is_sort == 1) {
                orders.push(`${TABLE_STRUCT[i].field_name}`)
            }
        }
    }

    const chooseData = cols.join(',');
    if (chooseData == '') {
        return errMsg(507, '未设置列表字段')
    }

    if (TABLE_INFO.is_distinct == 1) {
        chooseData = `DISTINCT ${chooseData}`
    }

    distincts = distincts.join(',')
    if (distincts != '') {
        chooseData = `DISTINCT ${distincts}`
    }

    groups = groups.join(',')
    if (groups != '') {
        searchStr += ` GROUP BY ${groups}`
    }

    orders = orders.join(',')
    if (orders != '') {
        searchStr += ` ORDER BY ${orders} ${TABLE_INFO.order_type == 1 ? 'DESC' : 'ASC'}`
    }

    const placeholdersObj = formatCondition();
    const res = genListPage(chooseData, placeholdersObj);
    return okMsg(res);
}

// 添加数据
function sqlGenInsert() {
    var data = payload.get().body.toObject()

    if (!data || (data && !Object.keys(data).length)) {
        return errMsg(404, '未传入参数')
    }

    let userAccess
    // 判断用户表权限
    if (TABLE_INFO.is_acl && !ALLOW_PUBLIC_ACCESS) {
        let udr = []
        udr.push(`'${USER_INFO.user_id}'`)
        udr.push(`'${USER_INFO.role_id}'`)
        udr.push(`'${USER_INFO.organization_id}'`)

        let columnIds = []
        TABLE_STRUCT.forEach(item => {
            if (item.is_acl == 1) {
                columnIds.push(`'${item.column_id}'`)
            }
        })

        userAccess = SQL.query(
            DB_NAME,
            `SELECT DISTINCT column_name FROM dm_table_columns_acl WHERE column_id IN (${columnIds.split(',')}) AND access_id IN (${udr.split(',')}) AND level = 2 AND delete_time IS NULL;`,
            TABLE_INFO.table_id
        )
    }

    var insertCols = []
    for (var i = 0; i < TABLE_STRUCT.length; i++) {
        if (TABLE_STRUCT[i].is_insert == 1) {
            if (TABLE_STRUCT[i].is_acl == 1 && !userAccess.includes(TABLE_STRUCT[i].column_name)) {
                continue
            }
            insertCols.push(TABLE_STRUCT[i].column_name)
        }
    }

    var dataArr = []
    var dataId = ex.suid().base58()
    var dataKeyArr = Object.keys(data)

    var cols = [COLUMN_KEY, 'proprietor_id', 'proprietor_name']
    var values = [`'${dataId}'`, `'${USER_INFO.user_id}'`, `'${USER_INFO.nickname}'`]
    let newItem

    for (let i = 0; i < dataKeyArr.length; i++) {
        newItem = TABLE_STRUCT.find(itemT => itemT.field_name === dataKeyArr[i])
        if (!newItem) {
            console.error('未找到字段:', dataKeyArr[i])
            return errMsg(404, '未找到字段')
        }
        if (insertCols.includes(newItem.column_name)) {
            cols.push(newItem.column_name)
            values.push('?')
            dataArr.push(data[dataKeyArr[i]])
        }
    }


    for (var i = 0; i < TABLE_STRUCT.length; i++) {
        if (TABLE_STRUCT[i].is_required == 1 && !cols.includes(TABLE_STRUCT[i].column_name)) {
            return errMsg(507, `未填写必选项:${TABLE_STRUCT[i].column_name}`)
        }
    }

    stmt = `INSERT INTO ${TABLE_NAME}(${cols.join(',')}) VALUES (${values.join(',')})`
    if (TABLE_INFO.before_hook) {
        result = runtime.call(TABLE_INFO.before_hook)
    }
    payload.add.header("sql_exec", stmt)
    result = SQL.exec(DB_NAME, stmt, ...dataArr)
    if (TABLE_INFO.after_hook) {
        runtime.call(TABLE_INFO.after_hook)
    }
    if (!result) {
        console.error('添加失败')
        return errMsg(507, '添加失败')
    }

    stmt = `SELECT access_id,level FROM dm_tables_acl WHERE table_id =? AND delete_time IS NULL`
    if (TABLE_INFO.before_hook) {
        result = runtime.call(TABLE_INFO.before_hook)
    }
    SQL_STMTS.push(stmt)
    result = SQL.query('system_sql', stmt, TABLE_INFO.table_id)
    if (TABLE_INFO.after_hook) {
        runtime.call(TABLE_INFO.after_hook)
    }

    if (result) {
        result.forEach(item => {
            switch (item.access_id) {
                case 'self':
                    item.access_id = USER_INFO.user_id
                    break
                case 'role':
                    item.access_id = USER_INFO.role_id
                    break
                case 'dept':
                    item.access_id = USER_INFO.organization_id
                    break
            }
            if (TABLE_INFO.before_hook) {
                runtime.call(TABLE_INFO.before_hook)
            }
            SQL.push('system_sql', `INSERT INTO sys_data_acl (data_id,access_id,level,proprietor_id,proprietor_name) VALUES (?,?,?,?,?)`, dataId, item.access_id, item.level, USER_INFO.user_id, USER_INFO.nickname)
        })
    } else {
        if (TABLE_INFO.before_hook) {
            runtime.call(TABLE_INFO.before_hook)
        }
        SQL.push('system_sql', `INSERT INTO sys_data_acl (data_id,access_id,level,proprietor_id,proprietor_name) VALUES (?,?,?,?,?)`, dataId, item.user_id, 3, USER_INFO.user_id, USER_INFO.nickname)
    }
    return okMsg(dataId)
}

// 基本的CURD功能
function base_curd() {
    payload.set.header("Databus-Time", new Date().getTime())

    let userAccess
    // 判断用户表权限
    if (TABLE_INFO.is_acl && !ALLOW_PUBLIC_ACCESS) {
        let udr = []
        udr.push(`'${USER_INFO.user_id}'`)
        udr.push(`'${USER_INFO.organization_id}'`)

        userAccess = SQL.query(
            'system_sql',
            `SELECT MAX(level) as level FROM dm_tables_acl WHERE table_id =? AND access_id IN (${udr.split(',')}) AND delete_time IS NULL;`,
            TABLE_INFO.table_id
        )

        if (userAccess) {
            userAccess = userAccess[0].level
        } else {
            return errMsg(403, '无权限')
        }
    }

    switch (payload.get().method) {
        case 'GET':
            if (TABLE_INFO.is_acl && userAccess < 1) {
                return errMsg(403, '无权限')
            }
            if (TABLE_INFO.is_acl && !getAclData(payload.get().query[COLUMN_KEY], 1)) {
                return errMsg(403, '无权限')
            }
            return sqlGenSelect()
        case 'PUT':
            if (TABLE_INFO.is_acl && userAccess < 2) {
                return errMsg(403, '无权限')
            }
            if (TABLE_INFO.is_acl && !getAclData(tableId, 1)) {
                return errMsg(403, '无权限')
            }
            return sqlGenUpdate()
        case 'POST':
            if (TABLE_INFO.is_acl && userAccess < 2) {
                return errMsg(403, '无权限')
            }
            return sqlGenInsert()
        case 'DELETE':
            if (TABLE_INFO.is_acl && userAccess < 3) {
                return errMsg(403, '无权限')
            }
            if (TABLE_INFO.is_acl && !getAclData(tableId, 3)) {
                return errMsg(403, '无权限')
            }
            return sqlGenDelete()
        default:
            response.status.notFound()
            return `404 page not found`
    }
}

// 通过sys_data_acl获取数据的权限
function getAclData(dataId, level) {
    let userAccess = SQL.query(
        'system_sql',
        `SELECT 1 FROM sys_data_acl WHERE data_id =? AND level > ? AND delete_time IS NULL;`,
        dataId, level - 1
    )

    return userAccess.length != 0
}